﻿using System;
using System.IO;
using System.Net;
using System.Net.Mail;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Hosting;
using System.Xml;
using umbraco.cms.businesslogic.member;
using umbraco.DataLayer;

namespace Designit.Umb.Newsletter.Bll
{
    public class EmbedNewsletter
    {

        public static string Emb(string templateAlias)
        {

            string template = "";
            //retrieve template code
            var path = Bll.Helper.GetTemplateDir();
            path = Path.Combine(path, string.Format("{0}.master", templateAlias));

            if (File.Exists(path))
            {
                template = File.ReadAllText(path);
            }

            //replace the custom body tag field
            string regex = @"<designit.Newsletter:Item field=""bodytext""></designit.Newsletter:Item>";
            string regex1 = @"<designit.Newsletter:Item field=""bodytext""/>";
            string replacement = @"<textarea id=""contentEditor"" name=""contentEditor"" style=""width:100%"" runat=""server""></textarea>";
            string result = Regex.Replace(template, regex + "|" + regex1, replacement, RegexOptions.IgnoreCase);



            //"build" the tinymce with custom button
            StringBuilder sb = new StringBuilder();
            var rnd = Guid.NewGuid();
            sb.AppendLine(string.Format(@"<script src=""/umbraco/plugins/tinymce3/tinymce3tinymceCompress.aspx?rnd={0}&amp;module=gzipmodule&amp;themes=umbraco&amp;plugins=contextmenu,table,umbracomacro,paste,inlinepopups,media,umbracocss,advimage,advlink,noneditable&amp;languages=en"" type=""text/javascript""></script>", rnd));
            sb.AppendLine(@"<link rel='stylesheet' href='/umbraco_client/menuicon/style.css' />");
            sb.AppendLine(@"<link rel='stylesheet' href='/umbraco_client/tabview/style.css' />");
            sb.AppendLine(@"<script type=""text/javascript"">");
            sb.AppendLine(@"// Creates a new plugin class and a custom listbox");
            sb.AppendLine(@"tinymce.create('tinymce.plugins.ExamplePlugin', {");
            sb.AppendLine(@"createControl: function(n, cm) {");
            sb.AppendLine(@"switch (n) {");
            sb.AppendLine(@"case 'tagmenubutton':");
            sb.AppendLine(@"var c = cm.createMenuButton('tagmenubutton', {");
            sb.AppendLine(@"title : 'Tag menu button',");
            sb.AppendLine(@"image : '/umbraco_client/tinymce/themes/advanced/images/button_menu.gif',");
            sb.AppendLine(@"icons : false");
            sb.AppendLine(@"});");
            sb.AppendLine(@"");
            sb.AppendLine(@"c.onRenderMenu.add(function(c, m) {");
            sb.AppendLine(@"var sub;");
            sb.AppendLine(@"m.add({title : '@SubscriberName', onclick : function() {");
            sb.AppendLine(@"tinyMCE.activeEditor.execCommand('mceInsertContent', false, '@SubscriberName');");
            sb.AppendLine(@"}});");
            sb.AppendLine(@"m.add({title : '@CompanyName', onclick : function() {");
            sb.AppendLine(@"tinyMCE.activeEditor.execCommand('mceInsertContent', false, '@CompanyName');");
            sb.AppendLine(@"}});");
            sb.AppendLine(@"m.add({title : '@Unsubscribe', onclick : function() {");
            sb.AppendLine(@"tinyMCE.activeEditor.execCommand('mceInsertContent', false, '@Unsubscribe');");
            sb.AppendLine(@"}});");
            sb.AppendLine(@"m.add({title : '@CompanyMail', onclick : function() {");
            sb.AppendLine(@"tinyMCE.activeEditor.execCommand('mceInsertContent', false, '@CompanyMail');");
            sb.AppendLine(@"}});");
            sb.AppendLine(@"});");
            sb.AppendLine(@"");
            sb.AppendLine(@"// Return the new menu button instance");
            sb.AppendLine(@"return c;");
            sb.AppendLine(@"}");
            sb.AppendLine(@"");
            sb.AppendLine(@"return null;");
            sb.AppendLine(@"}");
            sb.AppendLine(@"});");
            sb.AppendLine(@"");
            sb.AppendLine(@"// Register plugin with a short name");
            sb.AppendLine(@"tinymce.PluginManager.add('tagList', tinymce.plugins.ExamplePlugin);");
            sb.AppendLine(@"");
            sb.AppendLine(@"tinyMCE.init({");
            sb.AppendLine(@"mode:'exact',");
            sb.AppendLine(@"theme:'umbraco',");
            sb.AppendLine(@"umbraco_path:'/umbraco',");
            sb.AppendLine(@"theme_umbraco_toolbar_location:'top',");
            sb.AppendLine(@"skin:'umbraco',");
            sb.AppendLine(@"inlinepopups_skin :'umbraco',");
            sb.AppendLine(@"auto_resize : true,");
            sb.AppendLine(@"plugins:'contextmenu,table,tagList,paste,inlinepopups,media,umbracocss,advimage,advlink,noneditable',");
            sb.AppendLine(@"umbraco_advancedMode:true,");
            sb.AppendLine(@"umbraco_maximumDefaultImageWidth:'500',");
            sb.AppendLine(@"language:'en',");
            sb.AppendLine(@"content_css:'',");
            sb.AppendLine(@"theme_umbraco_styles:'',");
            sb.AppendLine(@"theme_umbraco_buttons1:'tagmenubutton,code,separator,undo,redo,cut,copy,pasteword,separator,umbracocss,bold,italic,separator,bullist,numlist,outdent,indent,separator,link,unlink,separator,image,table,separator,charmap,',");
            sb.AppendLine(@"theme_umbraco_buttons2:'',");
            sb.AppendLine(@"theme_umbraco_buttons3:'',");
            sb.AppendLine(@"theme_umbraco_toolbar_align:'left',");
            sb.AppendLine(@"setup : function(ed) {");
            sb.AppendLine(@"ed.onKeyUp.add(function(ed, e) {");
            sb.AppendLine(@"parent.resizeIframe('iframe1');");
            sb.AppendLine(@"});");
            sb.AppendLine(@"},");
            sb.AppendLine(@"theme_umbraco_disable:'help,visualaid,subscript,justifyright,justifyfull,inserthorizontalrule,underline,removeformat,strikethrough,media,superscript,justifyleft,justifycenter',");
            sb.AppendLine(@"theme_umbraco_path :true,");
            sb.AppendLine(@"extended_valid_elements:'div[*]',");
            sb.AppendLine(@"document_base_url:'/',");
            sb.AppendLine(@"relative_urls:false,");
            sb.AppendLine(@"remove_script_host:true,");
            sb.AppendLine(@"event_elements:'div',");
            sb.AppendLine(@"paste_auto_cleanup_on_paste:true,");
            sb.AppendLine(@"valid_elements:'+a[id|style|rel|rev|charset|hreflang|dir|lang|tabindex|accesskey|type|name|href|target|title|class|onfocus|onblur|onclick|' + ");
            sb.AppendLine(@"'ondblclick|onmousedown|onmouseup|onmouseover|onmousemove|onmouseout|onkeypress|onkeydown|onkeyup],-strong/-b[class|style],-em/-i[class|style],' + ");
            sb.AppendLine(@"'-strike[class|style],-u[class|style],#p[id|style|dir|class|align],-ol[class|style],-ul[class|style],-li[class|style],br,' + ");
            sb.AppendLine(@"'img[id|dir|lang|longdesc|usemap|style|class|src|onmouseover|onmouseout|border|alt=|title|hspace|vspace|width|height|align|umbracoorgwidth|umbracoorgheight|onresize|onresizestart|onresizeend|rel],' + ");
            sb.AppendLine(@"'-sub[style|class],-sup[style|class],-blockquote[dir|style],-table[border=0|cellspacing|cellpadding|width|height|class|align|summary|style|dir|id|lang|bgcolor|background|bordercolor],' + ");
            sb.AppendLine(@"'-tr[id|lang|dir|class|rowspan|width|height|align|valign|style|bgcolor|background|bordercolor],tbody[id|class],' + ");
            sb.AppendLine(@"'thead[id|class],tfoot[id|class],-td[id|lang|dir|class|colspan|rowspan|width|height|align|valign|style|bgcolor|background|bordercolor|scope],' + ");
            sb.AppendLine(@"'-th[id|lang|dir|class|colspan|rowspan|width|height|align|valign|style|scope],caption[id|lang|dir|class|style],-div[id|dir|class|align|style],' + ");
            sb.AppendLine(@"'-span[class|align],-pre[class|align|style],address[class|align|style],-h1[id|dir|class|align],-h2[id|dir|class|align],' + ");
            sb.AppendLine(@"'-h3[id|dir|class|align],-h4[id|dir|class|align],-h5[id|dir|class|align],-h6[id|style|dir|class|align],hr[class|style],' + ");
            sb.AppendLine(@"'dd[id|class|title|style|dir|lang],dl[id|class|title|style|dir|lang],dt[id|class|title|style|dir|lang],object[classid|width|height|codebase|*],' + ");
            sb.AppendLine(@"'param[name|value|_value],embed[type|width|height|src|*],map[name],area[shape|coords|href|alt|target],bdo,button,code',");
            sb.AppendLine(@"invalid_elements:'font',");
            sb.AppendLine(@"entity_encoding:'raw',");
            sb.AppendLine(@"theme_umbraco_versionId:'" + rnd + @"',");
            sb.AppendLine(@"elements:'contentEditor'");
            sb.AppendLine(@"});");
            sb.AppendLine(@"function getTinyContent(){");
            sb.AppendLine(@"var ed = tinyMCE.get('contentEditor');");
            sb.AppendLine(@"return ed.getContent();");
            sb.AppendLine(@"}");
            sb.AppendLine(@"function setTinyContent(htmlText){");
            sb.AppendLine(@"var ed = tinyMCE.get('contentEditor');");
            sb.AppendLine(@"ed.setContent(htmlText);");
            sb.AppendLine(@"}");
            sb.AppendLine(@"</script>");

            //check if header field exist in template:
            regex = "<head[^>]*>(.*?)</head>";
            if (Regex.IsMatch(template, regex, RegexOptions.IgnoreCase | RegexOptions.Singleline))
            {
                //insert javascript at the end of <head> field
                regex = @"</head>";
                replacement = sb.ToString() + "</head>";
                result = Regex.Replace(result, regex, replacement, RegexOptions.IgnoreCase);
                return result;
            }
            else
            {
                sb.AppendLine(result);
                return sb.ToString();
            }



        }

        public static string Embstep4(string templateAlias)
        {

            string template = "";
            //retrieve template code
            var path = Bll.Helper.GetTemplateDir();
            path = Path.Combine(path, string.Format("{0}.master", templateAlias));

            if (File.Exists(path))
            {
                template = File.ReadAllText(path);
            }

            //retrieve tinyMce content
            string campContent = umbraco.BusinessLogic.Application.SqlHelper.ExecuteScalar<string>("select tempContent from newsLetterCampaignTempContent where id=1");


            //replace the custom body tag field
            string regex = @"<designit.Newsletter:Item field=""bodytext""></designit.Newsletter:Item>";
            string regex1 = @"<designit.Newsletter:Item field=""bodytext""/>";
            string replacement = campContent;
            string result = Regex.Replace(template, regex + "|" + regex1, replacement, RegexOptions.IgnoreCase);

            return result;



        }

        public static string EmbSend(int campaignId)
        {

            //retrieve template code
            string template = umbraco.BusinessLogic.Application.SqlHelper.ExecuteScalar<string>("select campTemplatecontent from newsLetterCampaignNewsletter where id=@campaignId",
                umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@campaignId", campaignId));

            //retrieve newsletter content
            string campContent = umbraco.BusinessLogic.Application.SqlHelper.ExecuteScalar<string>("select campcontent from newsLetterCampaignNewsletter where id=@campaignId",
                umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@campaignId", campaignId));


            return EmbContentAndTemplate(template, campContent);

        }

        public static string EmbContentAndTemplate(string template, string content)
        {
            //replace the custom body tag field
            string regex = @"<designit.Newsletter:Item field=""bodytext""></designit.Newsletter:Item>";
            string regex1 = @"<designit.Newsletter:Item field=""bodytext""/>";
            string replacement = content;
            string result = Regex.Replace(template, regex + "|" + regex1, replacement, RegexOptions.IgnoreCase);

            return result;

        }

        public static void SendCampaign(int campaignId)
        {
            var campTitle = string.Empty;
            var campFromMail = string.Empty;
            var campSubject = string.Empty;
            var content = EmbSend(campaignId);
            var companyName = string.Empty;
            var companyMail = string.Empty;
            var campTrack = false;
            var campGoogle = false;

            using(var reader = umbraco.BusinessLogic.Application.SqlHelper.ExecuteReader("Select * from newsLetterCampaignNewsletter where id =@campaignId", umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@campaignId", campaignId)))
            {
                while (reader.Read())
                {
                    companyName = reader.GetString("campfrom");
                    companyMail = reader.GetString("campreplyMail");
                    campTrack = Convert.ToBoolean(reader.GetString("campTrack"));
                    campGoogle = Convert.ToBoolean(reader.GetString("campGoogle"));
                    campTitle = reader.GetString("camptitle");
                    campFromMail = reader.GetString("campreplyMail");
                    campSubject = reader.GetString("campsubject");
                }
            }
            //replace @companyname
            var regex = "@CompanyName";
            content = Regex.Replace(content, regex, companyName, RegexOptions.IgnoreCase);

            //replace @companymail
            regex = "@CompanyMail";
            content = Regex.Replace(content, regex, companyMail, RegexOptions.IgnoreCase);

            //set the smtp client and send the mail
            var doc = new XmlDocument();
            var path = @"/config/designit/newsletter/mailSettings.config";
            path = HostingEnvironment.MapPath(path);
            doc.Load(path);
            XmlNode xnode = doc.SelectSingleNode("//mailSettings/mailSetting[@name='Unsubscribe']");
            string unsubscribe = xnode.Attributes[1].Value;
            xnode = doc.SelectSingleNode("//mailSettings/mailSetting[@name='Smtp']");
            string smtp = xnode.Attributes[1].Value;
            xnode = doc.SelectSingleNode("//mailSettings/mailSetting[@name='SmtpPort']");
            string smtpPort = xnode.Attributes[1].Value;
            xnode = doc.SelectSingleNode("//mailSettings/mailSetting[@name='SmtpAuth']");
            string smtpAuth = xnode.Attributes[1].Value;
            xnode = doc.SelectSingleNode("//mailSettings/mailSetting[@name='Server']");
            string server = xnode.Attributes[1].Value;

            string trackerPage = server + "designit/newsletter/tracking/tracking.aspx";

            var cl = new SmtpClient(smtp, Int32.Parse(smtpPort));
            if (smtpAuth == "true")
            {
                xnode = doc.SelectSingleNode("//mailSettings/mailSetting[@name='UserId']");
                string userId = xnode.Attributes[1].Value;
                xnode = doc.SelectSingleNode("//mailSettings/mailSetting[@name='Password']");
                string password = xnode.Attributes[1].Value;
                cl.Credentials = new NetworkCredential(userId, password);
            }


            //replace href tags to redirect to counter page and store values of link in db
            regex = @"<a href=""(.*?)</a>";
            foreach (var match in Regex.Matches(content, regex, RegexOptions.IgnoreCase))
            {
                bool lokal = false;
                string url = Regex.Match(match.ToString(), @"""(.*?)""", RegexOptions.IgnoreCase).ToString();
                url = url.Substring(1, url.Length - 2);
                string replaceString = Regex.Match(match.ToString(), @"href=""(.*?)""").ToString();
                if (url.StartsWith("/{localLink"))
                {

                    url = umbraco.library.NiceUrlFullPath(Int32.Parse(url.Substring(12, url.Length - 13)));
                    url = server + url;
                    lokal = true;

                }
                if (campTrack)
                {
                    if (campGoogle)
                    {
                        var tracked = false;
                        //check if send from local server
                        if (url.Contains(server))
                        {
                            string campaignName = campTitle;
                            campaignName = Regex.Replace(campaignName, @"\s", "+", RegexOptions.IgnoreCase);
                            string markedText =
                                Regex.Match(match.ToString(), @""">(.*?)</a>", RegexOptions.IgnoreCase).ToString();
                            markedText = markedText.Substring(2, markedText.Length - 6);
                            string markedNonWhiteSpaceText = Regex.Replace(markedText, @"\s", "+",
                                                                           RegexOptions.IgnoreCase);
                            //check if url contains parameters
                            string regexLinkReplace = "";
                            if (url.Contains("?"))
                            {
                                regexLinkReplace = @"<a href=""" + url +
                                                   @"&utm_source=newsletter&utm_medium=email&utm_term=" +
                                                   markedNonWhiteSpaceText + "&utm_campaign=" + campaignName + @""">" +
                                                   markedText + "</a>";
                            }
                            else
                            {
                                regexLinkReplace = @"<a href=""" + url +
                                                   @"?utm_source=newsletter&utm_medium=email&utm_term=" +
                                                   markedNonWhiteSpaceText + "&utm_campaign=" + campaignName + @""">" +
                                                   markedText + "</a>";
                            }

                            content = Regex.Replace(content, match.ToString(), regexLinkReplace, RegexOptions.IgnoreCase);
                            tracked = true;
                        }

                        //check if it's a trusted site
                        if (!tracked)
                        {
                            XmlNodeList entryElements = doc.SelectNodes("//mailSettings/trustedSites/*");
                            if (entryElements != null)
                            {
                                for (int i = 0; i < entryElements.Count; i++)
                                {
                                    string trustedSite = entryElements[i].Attributes[0].Value.ToString();
                                    if (trustedSite.Contains("www."))
                                    {
                                        trustedSite = trustedSite.Substring(trustedSite.IndexOf('.') + 1);
                                    }
                                    else if (trustedSite.StartsWith("http"))
                                    {
                                        trustedSite = trustedSite.Substring(trustedSite.IndexOf('/') + 2);
                                    }

                                    if (url.Contains(trustedSite))
                                    {
                                        string campaignName = campTitle;
                                        campaignName = Regex.Replace(campaignName, @"\s", "+",
                                                                     RegexOptions.IgnoreCase);
                                        string markedText =
                                            Regex.Match(match.ToString(), @""">(.*?)</a>", RegexOptions.IgnoreCase).
                                                ToString();
                                        markedText = markedText.Substring(2, markedText.Length - 6);
                                        string markedNonWhiteSpaceText = Regex.Replace(markedText, @"\s", "+",
                                                                                       RegexOptions.IgnoreCase);
                                        //check if url contains parameters
                                        string regexLinkReplace = "";
                                        if (url.Contains("?"))
                                        {
                                            regexLinkReplace = @"<a href=""" + url +
                                                               @"&utm_source=newsletter&utm_medium=email&utm_term=" +
                                                               markedNonWhiteSpaceText + "&utm_campaign=" +
                                                               campaignName + @""">" + markedText + "</a>";
                                        }
                                        else
                                        {
                                            regexLinkReplace = @"<a href=""" + url +
                                                               @"?utm_source=newsletter&utm_medium=email&utm_term=" +
                                                               markedNonWhiteSpaceText + "&utm_campaign=" +
                                                               campaignName + @""">" + markedText + "</a>";
                                        }

                                        content = Regex.Replace(content, match.ToString(), regexLinkReplace,
                                                                RegexOptions.IgnoreCase);
                                        tracked = true;
                                        i = entryElements.Count;
                                    }
                                }
                            }
                        }

                        //track with own track
                        if (!tracked)
                        {
                            umbraco.BusinessLogic.Application.SqlHelper.ExecuteNonQuery(
                                "insert into newsletterTracking(campaignId, url, clicks) values(@campaignId, @url, 0)",
                                umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@campaignId", campaignId),
                                umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@url", url));
                            //check if url contains parameters
                            string regexLinkReplace = "";
                            if (url.Contains("?"))
                            {
                                regexLinkReplace = @"href=""" + trackerPage + "?redirect=" + url + "&campaign=" +
                                                   campaignId + @"""";
                            }
                            else
                            {
                                regexLinkReplace = @"href=""" + trackerPage + "?redirect=" + url + "&campaign=" +
                                                   campaignId + @"""";
                            }
                            content = Regex.Replace(content, replaceString, regexLinkReplace, RegexOptions.IgnoreCase);
                        }

                    }
                    else
                    {
                        umbraco.BusinessLogic.Application.SqlHelper.ExecuteNonQuery(
                            "insert into newsletterTracking(campaignId, url, clicks) values(@campaignId, @url, 0)",
                            umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@campaignId", campaignId),
                            umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@url", url));

                        //check if url contains parameters
                        string regexLinkReplace = "";
                        if (url.Contains("?"))
                        {
                            regexLinkReplace = @"href=""" + trackerPage + "?redirect=" + url + "&campaign=" + campaignId +
                                               @"""";
                        }
                        else
                        {
                            regexLinkReplace = @"href=""" + trackerPage + "?redirect=" + url + "&campaign=" + campaignId +
                                               @"""";
                        }
                        content = Regex.Replace(content, replaceString, regexLinkReplace, RegexOptions.IgnoreCase);
                    }

                }
                else if (lokal)
                {
                    string regexLocal = @"href=""" + url + @"""";
                    content = Regex.Replace(content, replaceString, regexLocal, RegexOptions.IgnoreCase);

                }
            }

            //Create an email and assign the sender of the mail
            var campaignMail = new MailMessage {From = new MailAddress(campFromMail)};
            string subject = campSubject;

            //retrieve memberId's assigned to the campaign
            using(var reader =
                umbraco.BusinessLogic.Application.SqlHelper.ExecuteReader("select * from newsLetterCampaign2Member where campaignId=@campaignID",
                                                                          umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@campaignId", campaignId)))
            {


                //store recievers and campaign id with send value = false
                while (reader.Read())
                {
                    umbraco.BusinessLogic.Application.SqlHelper.ExecuteNonQuery(
                        "insert into newsLetterSendMail(campaignId, memberId, Send) values(@campaignId, @memberId, @Send)",
                        umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@campaignId", campaignId),
                        umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@memberId",
                                                                                    reader.GetInt("memberId")),
                        umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@Send", "false"));
                }
            }




            //update reader to read from newsLetterSendMail
            using(var reader =
                umbraco.BusinessLogic.Application.SqlHelper.ExecuteReader("select memberId from newsLetterSendMail where campaignId=@campaignId",
                                                                          umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("campaignId", campaignId)))
            {

                //assign member's mail to recievers of the mail
                while (reader.Read())
                {
                    string mail =
                        umbraco.BusinessLogic.Application.SqlHelper.ExecuteScalar<string>(
                            "select Email from cmsMember where nodeId=@memberId",
                            umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@memberId",
                                                                                        reader.GetInt("memberId")));
                    campaignMail.To.Add(new MailAddress(mail));

                    //replace @Unsubscribe
                    regex = "@Unsubscribe";
                    var replacement =
                        string.Format(
                            @"<a title=""Unsubscribe"" href={0}?mail={1} target=""_blank"">Unsubscribe</a>",
                            unsubscribe, mail);
                    string finalCont = Regex.Replace(content, regex, replacement, RegexOptions.IgnoreCase);

                    //set subject of mail
                    campaignMail.Subject = subject;

                    //set the text of the mail
                    campaignMail.IsBodyHtml = true;

                    //replace @SubscriberName
                    regex = "@SubscriberName";
                    replacement = Member.GetMemberFromEmail(mail).LoginName;
                    finalCont = Regex.Replace(finalCont, regex, replacement, RegexOptions.IgnoreCase);
                    campaignMail.Body = finalCont;

                    cl.Send(campaignMail);

                    //update status on newsLetterSendMail for given member to this campaign
                    umbraco.BusinessLogic.Application.SqlHelper.ExecuteNonQuery(
                        "update newsLetterSendMail set Send='true'");
                    campaignMail.To.Clear();
                }
            }
            umbraco.BusinessLogic.Application.SqlHelper.ExecuteNonQuery("UPDATE newsletterCampaignNewsletter SET campstatus='sent', campSentTime=@time WHERE id=@campId",
                                                                            umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@campId", campaignId),
                                                                            umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@time",DateTime.Now));
            }

        }

    }
